﻿package component.tree.sql;
import component.tree.entity.Tree;
import component.tree.model.OrgTreeModel;
import component.tree.model.TreeModel;
import component.tree.util.StringUtil;


/*
--根据条件获取父
select parentTable.fid,parentTable.fpath,parentTable.fname,parentTable.fcode
from TFM_PB_TINSUBJ as parentTable,
(
  select FID, FPATH
  from TFM_PB_TINSUBJ
  where fname = '统筹收入'
) as temp
where left(temp.fpath,length(parentTable.fpath)) = parentTable.fpath

union --union会去掉相同的记录,union all就会有重复的记录 
  
--根据条件获取子
select childTable.fid,childTable.fpath,childTable.fname,childTable.fcode
from TFM_PB_TINSUBJ as childTable,
(
  select FID, FPATH
  from TFM_PB_TINSUBJ
  where fname = '统筹收入'
) as temp
where left(childTable.fpath,length(temp.fpath)) = temp.fpath
 */
public class CommonTreeSql extends DB2TreeSql{
	
	/**
	 * 查询的字段有：id,parentId,name,type,code,otherValue
	 * @param treeModel
	 * @return
	 */
	public String getTreeSql(TreeModel treeModel,boolean isQueryFirstLevel){
		StringBuffer sb = new StringBuffer(1000);

		sb.append("select ")
		  .append("treeTable.").append(treeModel.getFidName()).append(" as ").append(getFieldName(treeModel.getIdName())).append(" , ")
		  .append("treeTable.").append(treeModel.getFparentIdName()).append(" as ").append(getFieldName(treeModel.getParentIdName())).append(" , ")
		  .append("treeTable.").append(treeModel.getFnameName()).append(" as ").append(getFieldName(treeModel.getNameName())).append(" , ")
		  .append(treeModel.getFtypeName()).append(" as ").append(getFieldName(treeModel.getTypeName())).append(" , ")
		  .append("treeTable.").append(treeModel.getFcodeName()).append(" as ").append(getFieldName(treeModel.getCodeName())).append(" , ")
		  .append("treeTable.").append(treeModel.getFotherValueName()).append(" as ").append(getFieldName(treeModel.getOtherValueName())).append(" \n")
		  .append("from ").append(treeModel.getTableName()).append(" as treeTable , ")
		  .append("(")
		  .append("select ").append(treeModel.getFidName()).append(",").append(treeModel.getFpathName()).append(" ")
		  .append("from ").append(treeModel.getTableName()).append(" ")
		  .append("where 1=1 ").append(treeModel.getDefaultCondition()).append(" ");
		  if (!StringUtil.isNull(treeModel.getCondition())) {
			sb.append(treeModel.getCondition());
		  }
		  sb.append(") as temp \n")
		  .append("where left(temp.").append(treeModel.getFpathName()).append(",length(treeTable.").append(treeModel.getFpathName()).append(")) = treeTable.").append(treeModel.getFpathName()).append(" \n");
		  if (isQueryFirstLevel) {// 只查询第一层
			sb.append("and " + treeModel.getFparentIdName() + "='" + Tree.ROOT_ID + "' \n");
		  }
		 
		sb.append("\n");
		sb.append("union \n");
		sb.append("\n");
		  
		sb.append("select ")
		  .append("treeTable.").append(treeModel.getFidName()).append(" as ").append(getFieldName(treeModel.getIdName())).append(" , ")
		  .append("treeTable.").append(treeModel.getFparentIdName()).append(" as ").append(getFieldName(treeModel.getParentIdName())).append(" , ")
		  .append("treeTable.").append(treeModel.getFnameName()).append(" as ").append(getFieldName(treeModel.getNameName())).append(" , ")
		  .append(treeModel.getFtypeName()).append(" as ").append(getFieldName(treeModel.getTypeName())).append(" , ")
		  .append("treeTable.").append(treeModel.getFcodeName()).append(" as ").append(getFieldName(treeModel.getCodeName())).append(" , ")
		  .append("treeTable.").append(treeModel.getFotherValueName()).append(" as ").append(getFieldName(treeModel.getOtherValueName())).append(" \n")
		  .append("from ").append(treeModel.getTableName()).append(" as treeTable , ")
		  .append("(")
		  .append("select ").append(treeModel.getFidName()).append(",").append(treeModel.getFpathName()).append(" ")
		  .append("from ").append(treeModel.getTableName()).append(" ")
		  .append("where 1=1 ").append(treeModel.getDefaultCondition()).append(" ");
		  if (!StringUtil.isNull(treeModel.getCondition())) {
			sb.append(treeModel.getCondition());
		  }
		  sb.append(") as temp \n")
		    .append("where left(treeTable.").append(treeModel.getFpathName()).append(",length(temp.").append(treeModel.getFpathName()).append(")) = temp.").append(treeModel.getFpathName()).append(" \n");
		  if (isQueryFirstLevel) {// 只查询第一层
			sb.append("and " + treeModel.getFparentIdName() + "='"+ Tree.ROOT_ID + "' ");
		 }
		  
		return sb.toString();
	}
	
	public static void main(String[] args) {
		ITreeSql obj = new CommonTreeSql();
		TreeModel treeModel = new OrgTreeModel();
		System.out.println(obj.getTreeSql(treeModel, true));
	}
}
